from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        CREATE TABLE IF NOT EXISTS `product_category` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL UNIQUE COMMENT '分类名称',
    `description` LONGTEXT COMMENT '分类描述',
    `created_time` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_time` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `is_active` BOOL NOT NULL COMMENT '是否激活' DEFAULT 1,
    `parent_id` INT COMMENT '父级分类（NULL表示一级分类）',
    CONSTRAINT `fk_product__product__8a2045e6` FOREIGN KEY (`parent_id`) REFERENCES `product_category` (`id`) ON DELETE SET NULL
) CHARACTER SET utf8mb4 COMMENT='商品分类模型';
CREATE TABLE IF NOT EXISTS `product` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL COMMENT '商品名称',
    `description` LONGTEXT COMMENT '商品描述',
    `price` DECIMAL(10,2) NOT NULL COMMENT '商品价格',
    `image_url` VARCHAR(255) NOT NULL COMMENT '图片/视频URL',
    `video_url` VARCHAR(255) NOT NULL COMMENT '类型（image/video）',
    `inventory` INT NOT NULL COMMENT '商品库存' DEFAULT 0,
    `sales` INT NOT NULL COMMENT '商品销量' DEFAULT 0,
    `is_active` BOOL NOT NULL COMMENT '是否上架' DEFAULT 1,
    `is_hot` BOOL NOT NULL COMMENT '是否热销' DEFAULT 0,
    `share_count` INT NOT NULL COMMENT '分享次数' DEFAULT 0,
    `created_time` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_time` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `category_id` INT NOT NULL COMMENT '商品分类',
    CONSTRAINT `fk_product_product__97d0e89f` FOREIGN KEY (`category_id`) REFERENCES `product_category` (`id`) ON DELETE CASCADE,
    KEY `idx_product_name_683352` (`name`)
) CHARACTER SET utf8mb4 COMMENT='商品模型';
CREATE TABLE IF NOT EXISTS `product_spec` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL COMMENT '规格名称',
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `is_enabled` BOOL NOT NULL COMMENT '是否启用' DEFAULT 1,
    `product_id` INT NOT NULL COMMENT '商品',
    CONSTRAINT `fk_product__product_22898350` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='商品规格模型';
CREATE TABLE IF NOT EXISTS `product_spec_option` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL COMMENT '规格选项名称',
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `spec_id` INT NOT NULL COMMENT '规格',
    CONSTRAINT `fk_product__product__dcc59863` FOREIGN KEY (`spec_id`) REFERENCES `product_spec` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='商品规格选项模型';
CREATE TABLE IF NOT EXISTS `product_topping` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL COMMENT '小料名称',
    `price` DECIMAL(10,2) NOT NULL COMMENT '小料价格',
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `is_enabled` BOOL NOT NULL COMMENT '是否启用' DEFAULT 1,
    `product_id` INT NOT NULL,
    CONSTRAINT `fk_product__product_5596cd19` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='商品小料表';
CREATE TABLE IF NOT EXISTS `membership_level` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL COMMENT '会员等级名称',
    `upgrade_amount` DECIMAL(10,2) NOT NULL COMMENT '升级金额' DEFAULT 0,
    `level` INT NOT NULL COMMENT '等级数值',
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6)
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `user` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    `password` VARCHAR(128) NOT NULL COMMENT '密码',
    `email` VARCHAR(50) UNIQUE COMMENT '邮箱',
    `phone` VARCHAR(20) UNIQUE COMMENT '手机号',
    `user_type` VARCHAR(10) NOT NULL COMMENT '用户类型' DEFAULT '普通用户',
    `login_type` VARCHAR(10) NOT NULL COMMENT '登录类型' DEFAULT '本地',
    `third_party_id` VARCHAR(100) COMMENT '第三方ID',
    `third_party_account` VARCHAR(100) COMMENT '第三方账号',
    `third_party_phone` VARCHAR(20) COMMENT '第三方手机号',
    `create_time` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `update_time` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `avatar` VARCHAR(255) COMMENT '用户头像URL',
    `balance` DECIMAL(12,2) NOT NULL COMMENT '余额',
    `membership_level_id` INT COMMENT '会员等级',
    CONSTRAINT `fk_user_membersh_283e73af` FOREIGN KEY (`membership_level_id`) REFERENCES `membership_level` (`id`) ON DELETE SET NULL
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `address` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(20) NOT NULL,
    `address` VARCHAR(255) NOT NULL,
    `is_default` BOOL NOT NULL DEFAULT 0,
    `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `user_id` INT NOT NULL,
    CONSTRAINT `fk_address_user_a828eefb` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `fakeorders` (
    `id` BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
    `order_number` VARCHAR(64) NOT NULL UNIQUE COMMENT '商户订单号（雪花算法生成）',
    `status` VARCHAR(9) NOT NULL COMMENT '订单状态' DEFAULT 'pending',
    `total_amount` DECIMAL(12,2) NOT NULL COMMENT '订单总金额（元）',
    `actual_amount` DECIMAL(12,2) NOT NULL COMMENT '实际支付金额（元）',
    `payment_channel` VARCHAR(8) COMMENT '选择的支付渠道',
    `created_at` DATETIME(6) NOT NULL COMMENT '订单创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '订单更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `paid_at` DATETIME(6) COMMENT '支付成功时间',
    `cancelled_at` DATETIME(6) COMMENT '订单取消时间',
    `address_id` INT NOT NULL COMMENT '收货地址',
    `user_id` INT NOT NULL COMMENT '关联的用户',
    CONSTRAINT `fk_fakeorde_address_ef2eefce` FOREIGN KEY (`address_id`) REFERENCES `address` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_fakeorde_user_60aab9ca` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='订单表模型（模拟的订单表）';
CREATE TABLE IF NOT EXISTS `product_comment` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) COMMENT '评论名称',
    `rating` INT NOT NULL COMMENT '评分（1-5星）',
    `content` LONGTEXT NOT NULL COMMENT '评价内容',
    `images` VARCHAR(255) COMMENT '评价图片',
    `created_at` DATETIME(6) NOT NULL COMMENT '评价时间' DEFAULT CURRENT_TIMESTAMP(6),
    `reply_content` LONGTEXT COMMENT '回复内容',
    `is_auto` BOOL NOT NULL COMMENT '是否自动评价' DEFAULT 0,
    `order_id` BIGINT NOT NULL COMMENT '订单',
    `parent_id` INT COMMENT '父评论ID',
    `product_id` INT NOT NULL COMMENT '商品',
    `spec_option_id` INT COMMENT '购买的规格选项',
    `user_id` INT NOT NULL COMMENT '用户',
    CONSTRAINT `fk_product__fakeorde_91dd5d87` FOREIGN KEY (`order_id`) REFERENCES `fakeorders` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_product__product__cbe2eb43` FOREIGN KEY (`parent_id`) REFERENCES `product_comment` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_product__product_b396125e` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_product__product__e1b91340` FOREIGN KEY (`spec_option_id`) REFERENCES `product_spec_option` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_product__user_55d7c764` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='商品评价模型';
CREATE TABLE IF NOT EXISTS `orderitemfake` (
    `id` BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `quantity` INT NOT NULL COMMENT '购买数量',
    `price` DECIMAL(10,2) NOT NULL,
    `order_id` BIGINT NOT NULL,
    `product_id` INT NOT NULL,
    CONSTRAINT `fk_orderite_fakeorde_0adc6847` FOREIGN KEY (`order_id`) REFERENCES `fakeorders` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_orderite_product_2d69bacd` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='订单商品表模拟（关联订单与商品）';
CREATE TABLE IF NOT EXISTS `payment_records` (
    `id` BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '支付记录ID',
    `pay_amount` DECIMAL(12,2) NOT NULL COMMENT '支付金额（元）',
    `pay_status` VARCHAR(9) NOT NULL COMMENT '支付状态' DEFAULT 'pending',
    `pay_channel` VARCHAR(8) NOT NULL COMMENT '支付渠道',
    `pay_number` VARCHAR(64) NOT NULL UNIQUE COMMENT '支付编号（唯一，雪花算法生成）',
    `transaction_id` VARCHAR(64) COMMENT '第三方支付平台交易号',
    `created_at` DATETIME(6) NOT NULL COMMENT '支付记录创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '支付记录更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `timeout_at` DATETIME(6) NOT NULL COMMENT '支付超时时间（10分钟）',
    `order_id` BIGINT NOT NULL COMMENT '关联的订单',
    CONSTRAINT `fk_payment__fakeorde_693dd742` FOREIGN KEY (`order_id`) REFERENCES `fakeorders` (`id`) ON DELETE CASCADE,
    KEY `idx_payment_rec_order_i_4e7793` (`order_id`),
    KEY `idx_payment_rec_pay_sta_0d4420` (`pay_status`, `created_at`)
) CHARACTER SET utf8mb4 COMMENT='支付记录表模型（订单创建成功后才能生成）';
CREATE TABLE IF NOT EXISTS `history` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `created_time` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_time` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `browse_time` DATETIME(6) NOT NULL COMMENT '浏览时间' DEFAULT CURRENT_TIMESTAMP(6),
    `product_id` INT,
    `user_id` INT NOT NULL,
    CONSTRAINT `fk_history_product_b3042059` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_history_user_3d6556c0` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `purchase_history` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `created_time` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_time` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `browse_time` DATETIME(6) NOT NULL COMMENT '浏览时间' DEFAULT CURRENT_TIMESTAMP(6),
    `order_fake_id` BIGINT,
    `product_id` INT,
    `user_id` INT NOT NULL,
    CONSTRAINT `fk_purchase_fakeorde_7eb1260b` FOREIGN KEY (`order_fake_id`) REFERENCES `fakeorders` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_purchase_product_751f0b42` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_purchase_user_462cbf8a` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `aerich` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `version` VARCHAR(255) NOT NULL,
    `app` VARCHAR(100) NOT NULL,
    `content` JSON NOT NULL
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `homepagecarousel` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `images` VARCHAR(255) COMMENT '首页轮播图'
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `cart` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL COMMENT '用户ID(与用户绑定)',
    `product_id` INT NOT NULL COMMENT '商品ID',
    `product_name` VARCHAR(255) NOT NULL COMMENT '商品名称',
    `specs` VARCHAR(255) NOT NULL COMMENT '商品规格（如：尺寸:大; 口味:甜）',
    `toppings` VARCHAR(255) NOT NULL COMMENT '选中的小料' DEFAULT '',
    `quantity` INT NOT NULL COMMENT '商品数量' DEFAULT 1,
    `price` DECIMAL(10,2) NOT NULL COMMENT '加入时的单价（含小料）',
    `add_time` DATETIME(6) NOT NULL COMMENT '加入时间' DEFAULT CURRENT_TIMESTAMP(6),
    `update_time` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `image_url` VARCHAR(255) NOT NULL COMMENT '图片链接',
    UNIQUE KEY `uid_cart_user_id_3038aa` (`user_id`, `product_id`, `specs`, `toppings`),
    KEY `idx_cart_user_id_5e18ab` (`user_id`),
    KEY `idx_cart_user_id_3038aa` (`user_id`, `product_id`, `specs`, `toppings`)
) CHARACTER SET utf8mb4 COMMENT='购物车模型(适配Tortoise-ORM，物理删除逻辑)';"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        """
